/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package gestionrentabilite;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import sql.GestionBdd;

/**
 *
 * @author Quentin
 */
public class FenRenta extends javax.swing.JFrame {

    /**
     * Creates new form FenRenta
     */
    public FenRenta() {
        initComponents();
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jScrollPane1 = new javax.swing.JScrollPane();
        jTable1 = new javax.swing.JTable();
        cbxSession = new javax.swing.JComboBox();
        jLabel1 = new javax.swing.JLabel();
        lblLibelle = new javax.swing.JLabel();
        jLabel3 = new javax.swing.JLabel();
        jLabel4 = new javax.swing.JLabel();
        lblNiveau = new javax.swing.JLabel();
        jLabel5 = new javax.swing.JLabel();
        lblDate = new javax.swing.JLabel();
        jLabel6 = new javax.swing.JLabel();
        lblInscrits = new javax.swing.JLabel();
        jLabel7 = new javax.swing.JLabel();
        lblMarge = new javax.swing.JLabel();

        setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);
        addWindowListener(new java.awt.event.WindowAdapter() {
            public void windowOpened(java.awt.event.WindowEvent evt) {
                formWindowOpened(evt);
            }
        });

        jTable1.setModel(new ModeleJTableRenta());
        jScrollPane1.setViewportView(jTable1);

        cbxSession.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                cbxSessionItemStateChanged(evt);
            }
        });

        jLabel1.setText("Session :");

        lblLibelle.setName(""); // NOI18N

        jLabel3.setText("Libellé :");
        jLabel3.setName(""); // NOI18N

        jLabel4.setText("Niveau :");
        jLabel4.setName(""); // NOI18N

        lblNiveau.setName(""); // NOI18N

        jLabel5.setText("Date  :");
        jLabel5.setName(""); // NOI18N

        lblDate.setName(""); // NOI18N

        jLabel6.setText("Inscrits :");
        jLabel6.setName(""); // NOI18N

        lblInscrits.setName(""); // NOI18N

        jLabel7.setText("Marge :");
        jLabel7.setName(""); // NOI18N

        lblMarge.setText(" ");
        lblMarge.setName(""); // NOI18N

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(52, 52, 52)
                .addComponent(jLabel1)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(cbxSession, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(30, 30, 30)
                .addComponent(jLabel3)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(lblLibelle)
                .addGap(91, 91, 91)
                .addComponent(jLabel4)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(lblNiveau)
                .addGap(75, 75, 75)
                .addComponent(jLabel5)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(lblDate)
                .addGap(83, 83, 83)
                .addComponent(jLabel6)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(lblInscrits)
                .addGap(65, 65, 65)
                .addComponent(jLabel7)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(lblMarge)
                .addContainerGap(461, Short.MAX_VALUE))
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane1)
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap(33, Short.MAX_VALUE)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(cbxSession, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel1)
                    .addComponent(lblLibelle)
                    .addComponent(jLabel3)
                    .addComponent(jLabel4)
                    .addComponent(lblNiveau)
                    .addComponent(jLabel5)
                    .addComponent(lblDate)
                    .addComponent(jLabel6)
                    .addComponent(lblInscrits)
                    .addComponent(jLabel7)
                    .addComponent(lblMarge))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 253, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(21, 21, 21))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void formWindowOpened(java.awt.event.WindowEvent evt) {//GEN-FIRST:event_formWindowOpened
       try
            {
                // On prévoit 2 connexions à la base
                Statement stmt1 = GestionBdd.connexionBdd(GestionBdd.TYPE_MYSQL, "formarmor","localhost", "root","");
                
                String req = "Select * from session_form where close = 1";
                ResultSet rs = GestionBdd.envoiRequeteLMD(stmt1,req);
                cbxSession.addItem("---");
                while (rs.next())
                {
                    cbxSession.addItem(rs.getString(1));
                }
            }
            catch (SQLException se)
            {
                System.out.println("Erreur SQL1 : " + se.getMessage());
            }
    }//GEN-LAST:event_formWindowOpened

    private void cbxSessionItemStateChanged(java.awt.event.ItemEvent evt) {//GEN-FIRST:event_cbxSessionItemStateChanged
        if (evt.getStateChange()!=1){
            try
                {
                    // On prévoit 2 connexions à la base
                    Statement stmt1 = GestionBdd.connexionBdd(GestionBdd.TYPE_MYSQL, "formarmor","localhost", "root","");
                    //on remplis les labels
                    String req = "Select * from session_form where numero="+cbxSession.getSelectedItem();
                    ResultSet rs = GestionBdd.envoiRequeteLMD(stmt1,req);
                    SimpleDateFormat convert = new SimpleDateFormat("yyyy-MM-dd");
                    rs.first();
                    lblLibelle.setText(rs.getString("libelleform"));
                    lblNiveau.setText(rs.getString("niveauform"));
                    lblDate.setText(convert.format(rs.getDate("datedebut")));
                    lblInscrits.setText(Integer.toString(rs.getInt("nb_inscrits")));
                    
                    // calcule marge
                    req = "Select sum(taux_horaire) as revenu_session "
                    + "from statut st, session_form s, client c, inscription i "
                    + "where s.numero = i.num_session and c.matricule = i.matricule "
                    + "and c.typestatut = st.type and s.numero = " + cbxSession.getSelectedItem();
                    String req2="select * from formation"
                            + " where libelle='"+rs.getString("libelleform")
                            +"' and niveau='"+rs.getString("niveauform")+"'";
                    rs = GestionBdd.envoiRequeteLMD(stmt1,req);
                    rs.first();
                    int TauxHoraire = rs.getInt(1);
                    rs = GestionBdd.envoiRequeteLMD(stmt1,req2);
                    rs.first();
                    int marge = TauxHoraire - rs.getInt("coutrevient");
                    lblMarge.setText(Integer.toString(marge));
                    
                    
                    
                    
                   req = "Select A.matricule, nom, rue, cp, ville ,date_inscription, taux_horaire from "
                           + "client A, inscription B, statut C where A.matricule=B.matricule and A.typestatut=C.type"
                           + " and B.num_session="+cbxSession.getSelectedItem();
                   rs = GestionBdd.envoiRequeteLMD(stmt1,req);
                   int i = 0;
                   while(rs.next()){
                       jTable1.setValueAt(rs.getString("matricule"), i, 0);
                       jTable1.setValueAt(rs.getString("nom"), i, 1);
                       jTable1.setValueAt(rs.getString("rue"), i, 2);
                       jTable1.setValueAt(rs.getString("cp"), i, 3);
                       jTable1.setValueAt(rs.getString("ville"), i, 4);
                       jTable1.setValueAt(rs.getDate("date_inscription"), i, 5);
                       jTable1.setValueAt(rs.getInt("taux_horaire"), i, 6);
                       i++;
                   }
                   
                }
                catch (SQLException se)
                {
                    System.out.println("Erreur SQL1 : " + se.getMessage());
            }
        }
    }//GEN-LAST:event_cbxSessionItemStateChanged

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /* Set the Nimbus look and feel */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
         */
        try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(FenRenta.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(FenRenta.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(FenRenta.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(FenRenta.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>

        /* Create and display the form */
        java.awt.EventQueue.invokeLater(new Runnable() {
            @Override
            public void run() {
                new FenRenta().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JComboBox cbxSession;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JLabel jLabel7;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JTable jTable1;
    private javax.swing.JLabel lblDate;
    private javax.swing.JLabel lblInscrits;
    private javax.swing.JLabel lblLibelle;
    private javax.swing.JLabel lblMarge;
    private javax.swing.JLabel lblNiveau;
    // End of variables declaration//GEN-END:variables
}
